﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;


namespace IT_Pro.Class
{
   class DataServices
    {
      protected static String _connectionString = "Data Source=" + config() + "; Initial Catalog=ITPro; User ID=" + readcauhinh("user") + ";Password=" + readcauhinh("pass") + ";";
      public static SqlConnection connection = new SqlConnection(_connectionString);
      public static string Manv = string.Empty;
      public static string media=string.Empty;
      public static int checkSV = 1;
      // Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

        //public static void OpenConnection()
        public static void OpenConnection()
        {            
            try
            {
                //connection = new SqlConnection(_connectionString);
                connection.Open();
                
            }
            catch 
            {
              //  MessageBox.Show(ex.Message + " : Lỗi kết nối! kiểm tra lại máy chủ. ", "Lỗi kết nối..");
            }
        }

        //public static void CloseConnection()
        public static void CloseConnection()
        {
            if (connection != null)
            {
                connection.Close();
            }
        }
        public static DataTable ExecQuery(string sql)
        {
            OpenConnection();
            DataTable dt = new DataTable();
            SqlCommand command = connection.CreateCommand();
            //SqlCommand command = new SqlCommand(sql, connection);
            command.CommandText = sql;
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
            CloseConnection();
            return dt;
        }
        public static void ExecNonQuery(string sql)
        {
            OpenConnection();
            SqlCommand command = connection.CreateCommand();
            command.CommandText = sql;

            command.ExecuteNonQuery();
            CloseConnection();
        }
          

        private static string config()
        {
            string _config="";
            XmlTextReader xmlReader = new XmlTextReader("Config.xml");
            while (xmlReader.Read())
            {                
                if (xmlReader.Name == "server")
                {
                    _config = xmlReader.ReadElementString();
                }
            }
            xmlReader.Close();

            return _config;
        }

       public static string readcauhinh(string bien)
       {
           RegistryWriter rg = new RegistryWriter();
          return rg.valuekey(bien);
       }

       public static void ExecNonQueryArray(string[] strSQl, SqlCommand cmd)
       {
           for (int i = 0; i < strSQl.Length; i++)
           {
               cmd.CommandText = strSQl[i].ToString();
               cmd.ExecuteNonQuery();
           }
       }

       public static bool ExecuteSqlTransaction(string[] strSQl)
       {
           using (SqlConnection connect = new SqlConnection(_connectionString))
           {
               connect.Open();
               bool kq;
               SqlCommand command = connect.CreateCommand();
               SqlTransaction transaction;

               transaction = connect.BeginTransaction("Transaction");
               command.Connection = connect;
               command.Transaction = transaction;

               try
               {
                   ExecNonQueryArray(strSQl, command);
                   // Attempt to commit the transaction.
                   transaction.Commit();
                   kq = true;
                 //  Console.WriteLine("Both records are written to database.");
               }
               catch 
               {                 
                   // Attempt to roll back the transaction.
                   try
                   {
                       transaction.Rollback();
                       
                   }
                   catch 
                   {
                      
                   }
                   kq = false;
               }
               connect.Close();
               return kq;
           }
       }
      
      
       public static int ExcuteProcedureWithReturn(string procName, SqlParameter[] parameters)
       {
          
           try
           {
               OpenConnection();
               SqlCommand command = new SqlCommand();

               command.Connection = connection;

               command.CommandText = procName;

               command.CommandType = CommandType.StoredProcedure;

               if (parameters != null)
               {
                   foreach (SqlParameter para in parameters)
                   {
                       command.Parameters.Add(para);
                   }
               }

               SqlParameter returnVal = new SqlParameter("returnVal", SqlDbType.Int);
               returnVal.Direction = ParameterDirection.ReturnValue;
               command.Parameters.Add(returnVal);
               command.ExecuteNonQuery();
               CloseConnection();
               return Convert.ToInt32(returnVal.Value);              
           }
           catch (Exception ex)
           {
               throw new Exception("" + ex.Message);
           }
          
       }

       public static DataTable ExcuteProcedureGetTable(string procName, SqlParameter[] parameters)
       {
           try
           {
               OpenConnection();
               SqlCommand command = new SqlCommand();

               command.Connection = connection;

               command.CommandText = procName;

               command.CommandType = CommandType.StoredProcedure;

               if (parameters != null)
               {
                   foreach (SqlParameter para in parameters)
                   {
                       command.Parameters.Add(para);
                   }
               }

               SqlDataAdapter adapter = new SqlDataAdapter(command);
               DataSet ds = new DataSet("DATASET");
               adapter.Fill(ds);
               CloseConnection();
               return ds.Tables[0];
           }
           catch (Exception ex)
           {
               throw new Exception("Lỗi DataService.ExcuteProcedureGetTable() !!!\n" + ex.Message);
           }
       }
    }
}
